package com.pi.base.util;

import java.awt.Color;
import java.beans.IntrospectionException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.Collections;
import java.util.List;
import java.util.Objects;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.pi.base.util.bean.BeanUtil;


/**
 * POI工具类
 *
 * @author chenmfa
 * @since 2017-2-10 09:50:35
 */
@SuppressWarnings("unused")
public class ExcelUtil {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);

    private static final int HEX_RADIX = 16;
    /**
     * 前端12像素,10号字体
     */
    public static final Short DEFOULT_FONT_ZIZE = 10;
    /**
     * 前端12像素,15号字体
     */
    public static final Short HEADER_FONT_ZIZE = 15;
    /**
     * 默认字体
     */
    public static final String DEFAULT_FONT_NAME = "微软雅黑";
    /**
     * 需要右对齐的字段类型
     */
    public static final String[] GRID_ALIGN_RIGHT_FIELD_TYPE = {"F", "A", "N"};
    /**
     * 默认的标题名称
     */
    public static final String GRID_DEFAULT_TITLE = "数据导出";
    /**
     * 默认的页名称
     */
    private static final String DEFAULT_SHEET_NAME = GRID_DEFAULT_TITLE;

    /**
     * 头部表格高度
     */
    private static final short TOP_TABLE_HEIGHT = (short) (150 * 6);
    /**
     * 普通单元格宽度的倍率(像素和实际大小有一个倍率)
     */
    private static final short CELL_WIDTH = 36;
    /**
     * 普通单元格宽度的倍率(默认根据文字长度计算)
     */
    private static final short CELL_UNIT_WIDTH = 3;

    /**
     * @param color 颜色对象
     * @return Color 正常color对象
     */
    public static Color poiColor2awtColor(org.apache.poi.ss.usermodel.Color color) {
        java.awt.Color awtColor = null;
        if (color instanceof XSSFColor) {     //.xlsx格式,word 2007及以上
            XSSFColor xc = (XSSFColor) color;
            String rgbHex = xc.getARGBHex();
            if (rgbHex != null) {
                awtColor = new Color(Integer.parseInt(rgbHex.substring(2), HEX_RADIX));
            }
        } else if (color instanceof HSSFColor) {   //.xls格式,word 2003
            HSSFColor hc = (HSSFColor) color;
            short[] s = hc.getTriplet();
            if (s != null) {
                awtColor = new Color(s[0], s[1], s[2]);
            }
        }
        return awtColor;
    }

    public static Workbook importData2Excel(List<?> dataList, List<SysGridField> fieldList, POIParam param)
            throws IllegalAccessException, IllegalArgumentException,
            InvocationTargetException, IntrospectionException {
        SysGridInfo gridInfo = new SysGridInfo();
        gridInfo.setGridList(fieldList);
        gridInfo.setPoiParam(param);
        return importStandardData(dataList, gridInfo);
    }

    public static Workbook importStandardData(List<?> dataList, SysGridInfo gridInfo)
            throws IllegalAccessException, IllegalArgumentException,
            InvocationTargetException, IntrospectionException {
        Workbook workbook = new HSSFWorkbook();
        if (!checkGridInfo(gridInfo, dataList)) {
            return workbook;
        }

        Sheet sheet = workbook.createSheet(gridInfo.getPoiParam().getSheetName());
        //设置全局样式(用于小标题和数据)
        CellStyle globalStyle = getCellStyleLeft(workbook);

        int rowShifted = 0;
        if (StringUtils.isNotBlank(gridInfo.getPoiParam().getTitle())) {
            createTitle(workbook, sheet, gridInfo);
            rowShifted++;
        }

        rowShifted = createTimerRow(sheet, gridInfo, rowShifted, globalStyle);

        //标题行设置结束
        exportDataToWorkBook(workbook, sheet, globalStyle, gridInfo, dataList, 0, rowShifted);

        return workbook;
    }

    private static void createTitle(Workbook workbook, Sheet sheet, SysGridInfo gridInfo) {
        //设置表头数据样式
        CellStyle headerStyle = getCellStyleHeader(workbook);
        //创建Excel表头和查询时间行
        Row headerRow = sheet.createRow(0);
        //设置表头样式
        headerRow.setHeight(getRowHeight());
        Cell headerCell = headerRow.createCell(0);
        headerCell.setCellStyle(headerStyle);
        headerCell.setCellValue(gridInfo.getPoiParam().getTitle());
        //设置合并单元格样式
        int fieldSize = gridInfo.getGridList().size();
        CellRangeAddress headerCra = new CellRangeAddress(0, 0, 0, fieldSize);
        sheet.addMergedRegion(headerCra);
    }

    private static int createTimerRow(Sheet sheet, SysGridInfo gridInfo, int rowShifted, CellStyle globalStyle) {
        if (Objects.isNull(gridInfo.getPoiParam().getStartDate())
                || Objects.isNull(gridInfo.getPoiParam().getEndDate())) {
            return rowShifted;
        }
        //设置起止时间样式
        Row timerRow = sheet.createRow(rowShifted);

        Cell startCell = timerRow.createCell(0);
        startCell.setCellStyle(globalStyle);
        startCell.setCellValue("开始时间：" + gridInfo.getPoiParam().getStartDate());
        int timeCraWith = 2;
        //合并开始日期单元格
        CellRangeAddress startTimerCra = new CellRangeAddress(rowShifted, rowShifted, 0, timeCraWith);
        sheet.addMergedRegion(startTimerCra);

        int endCellStart = timeCraWith + 1;
        Cell endCell = timerRow.createCell(endCellStart);
        endCell.setCellStyle(globalStyle);
        endCell.setCellValue("结束时间：" + gridInfo.getPoiParam().getStartDate());
        //合并结束日期单元格
        CellRangeAddress stopTimerCra = new CellRangeAddress(rowShifted, rowShifted,
                endCellStart, endCellStart + timeCraWith);
        sheet.addMergedRegion(stopTimerCra);
        return ++rowShifted;
    }

    private static void exportDataToWorkBook(
            Workbook workbook, Sheet sheet, CellStyle globalStyle,
            SysGridInfo gridInfo, List<?> dataList, int startColumn, int rowShift)
            throws IllegalAccessException, IllegalArgumentException,
            InvocationTargetException, IntrospectionException {
        createRow(gridInfo, sheet, globalStyle, startColumn, rowShift);
        rowShift += 1;
        //开始填写数据内容
        Row contentRow;
        for (int m = 0, size = dataList.size(); m < size; m++) {
            Object object = dataList.get(m);
            contentRow = sheet.createRow(m + rowShift);
            for (int n = 0; n < gridInfo.getGridList().size(); n++) {
                SysGridField field = gridInfo.getGridList().get(n);
                String fieldName = field.getFieldName();
                Cell contentcell = contentRow.createCell(n + startColumn);
                contentcell.setCellStyle(globalStyle);
                Object o = BeanUtil.getproperty(object, fieldName);
                contentcell.setCellValue(null != o ? o.toString() : "");
            }
            int affectRows =
                    handleChild(workbook, sheet, globalStyle, gridInfo, object, startColumn, m + rowShift + 1);
            rowShift += affectRows;
        }
    }

    private static void createRow(
            SysGridInfo gridInfo, Sheet sheet, CellStyle globalStyle,
            int startColumn, int rowShift) {
        List<SysGridField> fieldList = gridInfo.getGridList();

        //标题行
        Row titleRow = sheet.createRow(rowShift);
        for (int i = 0; i < fieldList.size(); i++) {
            SysGridField field = fieldList.get(i);
            Cell titlecell = titleRow.createCell(i + startColumn);
            titlecell.setCellStyle(globalStyle);
            String alias = field.getFieldAlias();
            if (StringUtils.isBlank(alias)) {
                alias = GRID_DEFAULT_TITLE;
            }
            titlecell.setCellValue(alias);
            Integer fieldWidth = field.getFieldWidth();
            if (null != fieldWidth && fieldWidth > 0) {
                sheet.setColumnWidth(i, getCellWidth(fieldWidth));
            } else {
                sheet.setColumnWidth(i, getCellWidth(alias.length() * CELL_UNIT_WIDTH));
            }
        }
    }

    private static int handleChild(
            Workbook workbook, Sheet sheet, CellStyle globalStyle,
            SysGridInfo gridInfo, Object data, int startColumn, int rowShift)
            throws IllegalArgumentException, IllegalAccessException,
            InvocationTargetException, IntrospectionException {
        //处理子列事件(这里的逻辑是这样的，如果有配置子列的属性名，则使用指定的， 没有的话，自动搜索到以一个匹配的list)
        if (null != gridInfo.getChildGrid()) {
            List<?> childList = StringUtils.isBlank(gridInfo.getChildGridName()) ? getDataListFromData(data)
                    : getDataListFromChild(data, gridInfo.getChildGridName());
            if (CollectionUtils.isEmpty(childList)) {
                LOGGER.error("[child_grid_data_not_found] {}", gridInfo.getChildGridName());
                return 0;
            }
            exportDataToWorkBook(workbook, sheet, globalStyle,
                    gridInfo.getChildGrid(), childList, startColumn + 1, rowShift);
            return childList.size() + 1;
        }
        return 0;
    }

    private static List<?> getDataListFromChild(Object data, String childName) throws IllegalAccessException,
            IntrospectionException, InvocationTargetException {
        if (!BeanUtil.hasField(data.getClass(), childName)) {
            LOGGER.warn("[child_grid_not_exist]: {}", childName);
            return Collections.emptyList();
        }
        List<?> childList =
                BeanUtil.getproperty(data, childName, new Object[]{});
        if (null == childList) {
            LOGGER.warn("[child_grid_pop_not_match]: {}", childName);
            return Collections.emptyList();
        }
        return childList;
    }

    private static List<?> getDataListFromData(Object data) throws IllegalAccessException {
        Field[] filedArr = BeanUtil.getFiledListFromClz(data.getClass());
        for (Field fieldClz : filedArr) {
            if (fieldClz.getType().isAssignableFrom(List.class)) {
                fieldClz.setAccessible(true);
                return (List<?>) fieldClz.get(data);
            }
        }
        return Collections.emptyList();
    }

    @SuppressWarnings("checkstyle:CyclomaticComplexity")
    private static boolean checkGridInfo(SysGridInfo gridInfo, List<?> dataList) {
        if (null == gridInfo) {
            LOGGER.error("表格字段信息为空");
            return false;
        }
        if (null == gridInfo.getPoiParam()) {
            LOGGER.error("表格参数为空");
            return false;
        }
        if (CollectionUtils.isEmpty(gridInfo.getGridList())) {
            LOGGER.error("表格头信息为空");
            return false;
        }
        if (CollectionUtils.isEmpty(dataList)) {
            LOGGER.error("表格数据信息为空");
            return false;
        }
        if (StringUtils.isBlank(gridInfo.getPoiParam().getTitle()) && BooleanUtils.isTrue(gridInfo.getInitDefaultTitle())) {
            gridInfo.getPoiParam().setTitle(DEFAULT_SHEET_NAME);
        }
        if (StringUtils.isBlank(gridInfo.getPoiParam().getSheetName())) {
            gridInfo.getPoiParam().setSheetName(DEFAULT_SHEET_NAME);
        }
        return true;
    }

    /**
     * 头部标题样式选择(水平 ， 垂直都居中)
     *
     * @param workbook excel表对象
     */
    protected static CellStyle getCellStyleHeader(Workbook workbook) {
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        headerStyle.setFont(getHeaderFontStyle(workbook));
        return headerStyle;
    }

    /**
     * 左对齐样式
     *
     * @param workbook excel表对象
     */
    protected static CellStyle getCellStyleLeft(Workbook workbook) {
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setFont(getFontStyle(workbook));
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        return titleStyle;
    }

    /**
     * 右对齐样式
     *
     * @param workbook excel表格对象
     */
    protected static CellStyle getCellStyleRight(Workbook workbook) {
        CellStyle tableStyle = workbook.createCellStyle();
        // 默认左对齐
        tableStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        tableStyle.setFont(getFontStyle(workbook));
        return tableStyle;
    }

    /**
     * 获取默认字体样式
     *
     * @param workbook excel表格对象
     */
    protected static Font getFontStyle(Workbook workbook) {
        Font fontStyle = workbook.createFont();
        fontStyle.setFontHeightInPoints(DEFOULT_FONT_ZIZE);
        fontStyle.setFontName(DEFAULT_FONT_NAME);
        return fontStyle;
    }

    /**
     * 获取头部字体样式(大小 : HEADER_FONT_ZIZE 字体 : DEFOULT_FONT_NAME)
     *
     * @param workbook excel表格对象
     */
    protected static Font getHeaderFontStyle(Workbook workbook) {
        Font fontStyle = workbook.createFont();
        fontStyle.setFontHeightInPoints(HEADER_FONT_ZIZE);
        fontStyle.setFontName(DEFAULT_FONT_NAME);
        return fontStyle;
    }

    /**
     * 像素转宽度
     */
    protected static int getCellWidth(int pixel) {
        return pixel * CELL_WIDTH;
    }

    /**
     * 获取头部表格高度
     */
    protected static short getRowHeight() {
        return TOP_TABLE_HEIGHT;
    }
}
